import express from 'express';
import { dbPool } from '../config/database.js';

const router = express.Router();

// 获取产品列表
router.get('/', async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 10, 
      search = '', 
      category = '',
      status = 'active',
      sortBy = 'created_at',
      sortOrder = 'DESC'
    } = req.query;
    
    const offset = (page - 1) * limit;
    
    let query = `
      SELECT id, name, description, price, category, image_url, 
             stock_quantity, status, created_at, updated_at 
      FROM products 
      WHERE status = $1
    `;
    
    let countQuery = 'SELECT COUNT(*) FROM products WHERE status = $1';
    const params = [status];
    let paramIndex = 1;
    
    if (search) {
      query += ` AND (name ILIKE $${++paramIndex} OR description ILIKE $${paramIndex})`;
      countQuery += ` AND (name ILIKE $${paramIndex} OR description ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
    }
    
    if (category) {
      query += ` AND category = $${++paramIndex}`;
      countQuery += ` AND category = $${paramIndex}`;
      params.push(category);
    }
    
    // 添加排序
    const validSortFields = ['name', 'price', 'created_at', 'updated_at'];
    const sortField = validSortFields.includes(sortBy) ? sortBy : 'created_at';
    const order = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
    
    query += ` ORDER BY ${sortField} ${order} LIMIT $${++paramIndex} OFFSET $${++paramIndex}`;
    params.push(parseInt(limit), offset);
    
    const [productsResult, countResult] = await Promise.all([
      dbPool.query(query, params),
      dbPool.query(countQuery, params.slice(0, paramIndex - 2))
    ]);
    
    res.json({
      success: true,
      data: {
        products: productsResult.rows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total: parseInt(countResult.rows[0].count),
          totalPages: Math.ceil(parseInt(countResult.rows[0].count) / limit)
        }
      }
    });
  } catch (error) {
    console.error('获取产品列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取产品列表失败'
    });
  }
});

// 获取产品详情
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await dbPool.query(
      `SELECT id, name, description, price, category, image_url, 
              stock_quantity, status, created_at, updated_at 
       FROM products WHERE id = $1`,
      [id]
    );
    
    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '产品不存在'
      });
    }
    
    res.json({
      success: true,
      data: result.rows[0]
    });
  } catch (error) {
    console.error('获取产品详情错误:', error);
    res.status(500).json({
      success: false,
      message: '获取产品详情失败'
    });
  }
});

// 创建产品
router.post('/', async (req, res) => {
  try {
    const { name, description, price, category, image_url, stock_quantity = 0 } = req.body;
    
    // 验证必填字段
    if (!name || !price) {
      return res.status(400).json({
        success: false,
        message: '产品名称和价格不能为空'
      });
    }
    
    // 检查产品名称是否重复
    const duplicateCheck = await dbPool.query(
      'SELECT id FROM products WHERE name = $1',
      [name]
    );
    
    if (duplicateCheck.rows.length > 0) {
      return res.status(409).json({
        success: false,
        message: '产品名称已存在'
      });
    }
    
    // 创建产品
    const result = await dbPool.query(
      `INSERT INTO products (name, description, price, category, image_url, stock_quantity, status)
       VALUES ($1, $2, $3, $4, $5, $6, 'active')
       RETURNING id, name, description, price, category, image_url, stock_quantity, status, created_at`,
      [name, description, parseFloat(price), category, image_url, parseInt(stock_quantity)]
    );
    
    res.status(201).json({
      success: true,
      message: '产品创建成功',
      data: result.rows[0]
    });
  } catch (error) {
    console.error('创建产品错误:', error);
    res.status(500).json({
      success: false,
      message: '创建产品失败'
    });
  }
});

// 更新产品
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, description, price, category, image_url, stock_quantity, status } = req.body;
    
    // 检查产品是否存在
    const productCheck = await dbPool.query('SELECT id FROM products WHERE id = $1', [id]);
    if (productCheck.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '产品不存在'
      });
    }
    
    // 检查产品名称是否重复（排除当前产品）
    if (name) {
      const duplicateCheck = await dbPool.query(
        'SELECT id FROM products WHERE name = $1 AND id != $2',
        [name, id]
      );
      
      if (duplicateCheck.rows.length > 0) {
        return res.status(409).json({
          success: false,
          message: '产品名称已存在'
        });
      }
    }
    
    // 构建更新字段
    const updateFields = [];
    const params = [id];
    let paramIndex = 1;
    
    if (name) {
      updateFields.push(`name = $${++paramIndex}`);
      params.push(name);
    }
    if (description !== undefined) {
      updateFields.push(`description = $${++paramIndex}`);
      params.push(description);
    }
    if (price) {
      updateFields.push(`price = $${++paramIndex}`);
      params.push(parseFloat(price));
    }
    if (category !== undefined) {
      updateFields.push(`category = $${++paramIndex}`);
      params.push(category);
    }
    if (image_url !== undefined) {
      updateFields.push(`image_url = $${++paramIndex}`);
      params.push(image_url);
    }
    if (stock_quantity !== undefined) {
      updateFields.push(`stock_quantity = $${++paramIndex}`);
      params.push(parseInt(stock_quantity));
    }
    if (status) {
      updateFields.push(`status = $${++paramIndex}`);
      params.push(status);
    }
    
    if (updateFields.length === 0) {
      return res.status(400).json({
        success: false,
        message: '没有提供更新数据'
      });
    }
    
    updateFields.push('updated_at = CURRENT_TIMESTAMP');
    
    const query = `
      UPDATE products SET ${updateFields.join(', ')} 
      WHERE id = $1 
      RETURNING id, name, description, price, category, image_url, stock_quantity, status, updated_at
    `;
    
    const result = await dbPool.query(query, params);
    
    res.json({
      success: true,
      message: '产品更新成功',
      data: result.rows[0]
    });
  } catch (error) {
    console.error('更新产品错误:', error);
    res.status(500).json({
      success: false,
      message: '更新产品失败'
    });
  }
});

// 删除产品
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // 检查产品是否存在
    const productCheck = await dbPool.query('SELECT id FROM products WHERE id = $1', [id]);
    if (productCheck.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '产品不存在'
      });
    }
    
    // 软删除（更新状态为inactive）
    await dbPool.query(
      'UPDATE products SET status = $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2',
      ['inactive', id]
    );
    
    res.json({
      success: true,
      message: '产品删除成功'
    });
  } catch (error) {
    console.error('删除产品错误:', error);
    res.status(500).json({
      success: false,
      message: '删除产品失败'
    });
  }
});

// 搜索产品
router.get('/search/:query', async (req, res) => {
  try {
    const { query } = req.params;
    const { limit = 20 } = req.query;
    
    const result = await dbPool.query(
      `SELECT id, name, description, price, category, image_url 
       FROM products 
       WHERE status = 'active' AND (name ILIKE $1 OR description ILIKE $1)
       ORDER BY name LIMIT $2`,
      [`%${query}%`, parseInt(limit)]
    );
    
    res.json({
      success: true,
      data: result.rows
    });
  } catch (error) {
    console.error('搜索产品错误:', error);
    res.status(500).json({
      success: false,
      message: '搜索产品失败'
    });
  }
});

export default router;